Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports Microsoft.ApplicationBlocks.Data
Imports DotNetNuke

Namespace SGW.ComunicadorWeb

    Public Class SqlDataProvider

        Inherits DataProvider

#Region "Private Members"

        Private Const ProviderType As String = "data"
        Private Const ModuleQualifier As String = "SGW_"

        Private _providerConfiguration As Framework.Providers.ProviderConfiguration = Framework.Providers.ProviderConfiguration.GetProviderConfiguration(ProviderType)
        Private _connectionString As String
        Private _providerPath As String
        Private _objectQualifier As String
        Private _databaseOwner As String

#End Region

#Region "Constructors"

        Public Sub New()

            ' Read the configuration specific information for this provider
            Dim objProvider As Framework.Providers.Provider = CType(_providerConfiguration.Providers(_providerConfiguration.DefaultProvider), Framework.Providers.Provider)

            ' Read the attributes for this provider
            If objProvider.Attributes("connectionStringName") <> "" AndAlso _
            System.Configuration.ConfigurationManager.AppSettings(objProvider.Attributes("connectionStringName")) <> "" Then
                _connectionString = System.Configuration.ConfigurationManager.AppSettings(objProvider.Attributes("connectionStringName"))
            Else
                _connectionString = objProvider.Attributes("connectionString")
            End If

            _providerPath = objProvider.Attributes("providerPath")

            _objectQualifier = objProvider.Attributes("objectQualifier")
            If _objectQualifier <> "" And _objectQualifier.EndsWith("_") = False Then
                _objectQualifier += "_"
            End If

            _databaseOwner = objProvider.Attributes("databaseOwner")
            If _databaseOwner <> "" And _databaseOwner.EndsWith(".") = False Then
                _databaseOwner += "."
            End If

        End Sub

#End Region

#Region "Properties"

        Public ReadOnly Property ConnectionString() As String
            Get
                Return _connectionString
            End Get
        End Property

        Public ReadOnly Property ProviderPath() As String
            Get
                Return _providerPath
            End Get
        End Property

        Public ReadOnly Property ObjectQualifier() As String
            Get
                Return _objectQualifier
            End Get
        End Property

        Public ReadOnly Property DatabaseOwner() As String
            Get
                Return _databaseOwner
            End Get
        End Property

#End Region

#Region "Public Methods"

        Public Overrides Function GetComunicadorWebs(ByVal PortalId As Integer, ByVal UserID As Integer) As IDataReader
            Return CType(SqlHelper.ExecuteReader(ConnectionString, GetFullyQualifiedName("GetComunicadorWebs"), PortalId, UserID), IDataReader)
        End Function

        Public Overrides Function GetComunicadorWebsActivos(ByVal PortalId As Integer, ByVal UserID As Integer) As IDataReader
            Return CType(SqlHelper.ExecuteReader(ConnectionString, GetFullyQualifiedName("GetComunicadorWebsActivos"), PortalId, UserID), IDataReader)
        End Function

        Public Overrides Function GetComunicadorWebsCerrados(ByVal PortalId As Integer, ByVal UserID As Integer, ByVal PageSize As Integer, ByVal CurrentPage As Integer) As IDataReader
            Return CType(SqlHelper.ExecuteReader(ConnectionString, GetFullyQualifiedName("GetComunicadorWebsCerrados"), PortalId, UserID, PageSize, CurrentPage), IDataReader)
        End Function

        Public Overrides Function GetAllComunicadorWebs(ByVal PortalId As Integer) As IDataReader
            Return CType(SqlHelper.ExecuteReader(ConnectionString, GetFullyQualifiedName("GetAllComunicadorWebs"), PortalId), IDataReader)
        End Function


        'Public Overrides Function GetComunicadorWebsSorted(ByVal PortalId As Integer, ByVal UserID As Integer, ByVal SortClause As String) As IDataReader
        '    Return CType(SqlHelper.ExecuteReader(ConnectionString, GetFullyQualifiedName("GetComunicadorWebsSorted"), PortalId, SortClause), IDataReader)
        'End Function


        'Public Overrides Function GetAllComunicadorWebsSorted(ByVal PortalId As Integer, ByVal SortClause As String) As IDataReader
        '    Return CType(SqlHelper.ExecuteReader(ConnectionString, GetFullyQualifiedName("GetAllComunicadorWebsSorted"), SortClause), IDataReader)
        'End Function

        Public Overrides Function GetComunicadorWeb(ByVal PortalId As Integer, ByVal HiloID As Integer) As IDataReader
            Return CType(SqlHelper.ExecuteReader(ConnectionString, GetFullyQualifiedName("GetComunicadorWeb"), PortalId, HiloID), IDataReader)
        End Function

        Public Overrides Function AddComunicadorWeb(ByVal PortalId As Integer, ByVal Hil_FechaCreacion As DateTime, ByVal Hil_Status As Int32, ByVal Hil_UsuarioID As Int32, ByVal Hil_IDUltimoMensaje As Int32, ByVal Hil_Cerrado As Boolean, ByVal Hil_FechaCierre As DateTime, ByVal Hil_PrioridadId As Int32, ByVal Hil_CategoriaID As Int32, ByVal Hil_SubCategoriaID As Int32, ByVal Hil_Asunto As String) As Integer
            ' Create Instance of Connection and Command Object
            Dim myConnection As New SqlConnection(ConnectionString)
            Dim myCommand As New SqlCommand(GetFullyQualifiedName("AddComunicadorWeb"), myConnection)

            ' Mark the Command as a SPROC
            myCommand.CommandType = CommandType.StoredProcedure

            ' Add Parameters to SPROC
            Dim parameterID As New SqlParameter("@ID", SqlDbType.Int, 4)
            parameterID.Direction = ParameterDirection.Output
            myCommand.Parameters.Add(parameterID)

            Dim parameterPortalId As New SqlParameter("@PortalID", SqlDbType.Int)
            parameterPortalId.Value = PortalId
            myCommand.Parameters.Add(parameterPortalId)

            Dim parameterHil_FechaCreacion As New SqlParameter("@Hil_FechaCreacion", SqlDbType.DateTime)
            parameterHil_FechaCreacion.Value = Hil_FechaCreacion
            myCommand.Parameters.Add(parameterHil_FechaCreacion)

            Dim parameterHil_Status As New SqlParameter("@Hil_Status", SqlDbType.Bit)
            parameterHil_Status.Value = Hil_Status
            myCommand.Parameters.Add(parameterHil_Status)

            Dim parameterHil_UsuarioID As New SqlParameter("@Hil_UsuarioID", SqlDbType.Int)
            parameterHil_UsuarioID.Value = Hil_UsuarioID
            myCommand.Parameters.Add(parameterHil_UsuarioID)

            Dim parameterHil_IDUltimoMensaje As New SqlParameter("@Hil_IDUltimoMensaje", SqlDbType.Int)
            parameterHil_IDUltimoMensaje.Value = Hil_IDUltimoMensaje
            myCommand.Parameters.Add(parameterHil_IDUltimoMensaje)

            Dim parameterHil_Cerrado As New SqlParameter("@Hil_Cerrado", SqlDbType.Bit)
            parameterHil_Cerrado.Value = Hil_Cerrado
            myCommand.Parameters.Add(parameterHil_Cerrado)

            Dim parameterHil_FechaCierre As New SqlParameter("@Hil_FechaCierre", SqlDbType.DateTime)
            parameterHil_FechaCierre.Value = GetNull(Hil_FechaCierre)
            myCommand.Parameters.Add(parameterHil_FechaCierre)

            Dim parameterHil_PrioridadId As New SqlParameter("@Hil_PrioridadId", SqlDbType.Int)
            parameterHil_PrioridadId.Value = Hil_PrioridadId
            myCommand.Parameters.Add(parameterHil_PrioridadId)

            Dim parameterHil_CategoriaID As New SqlParameter("@Hil_CategoriaID", SqlDbType.Int)
            parameterHil_CategoriaID.Value = Hil_CategoriaID
            myCommand.Parameters.Add(parameterHil_CategoriaID)

            Dim parameterHil_SubCategoriaID As New SqlParameter("@Hil_SubCategoriaID", SqlDbType.Int)
            parameterHil_SubCategoriaID.Value = Hil_SubCategoriaID
            myCommand.Parameters.Add(parameterHil_SubCategoriaID)

            Dim parameterHil_Asunto As New SqlParameter("@Hil_Asunto", SqlDbType.NVarChar, 255)
            parameterHil_Asunto.Value = Hil_Asunto
            myCommand.Parameters.Add(parameterHil_Asunto)



            ' Open the database connection and execute SQL Command
            myConnection.Open()
            myCommand.ExecuteNonQuery()
            myCommand.Dispose()
            myConnection.Close()

            ' Return the new Event ItemID
            Return CInt(parameterID.Value)

            'SqlHelper.ExecuteNonQuery(ConnectionString, GetFullyQualifiedName("AddComunicadorWeb"), PortalId, Hil_FechaCreacion, Hil_Status, Hil_UsuarioID, Hil_IDUltimoMensaje, Hil_Cerrado, Hil_FechaCierre, Hil_PrioridadId, Hil_CategoriaID, Hil_SubCategoriaID, Hil_Asunto)
        End Function

        Public Overrides Sub UpdateComunicadorWeb(ByVal PortalId As Integer, ByVal HiloID As Int32, ByVal Hil_FechaCreacion As DateTime, ByVal Hil_Status As Int32, ByVal Hil_UsuarioID As Int32, ByVal Hil_IDUltimoMensaje As Int32, ByVal Hil_Cerrado As Boolean, ByVal Hil_FechaCierre As DateTime, ByVal Hil_PrioridadId As Int32, ByVal Hil_CategoriaID As Int32, ByVal Hil_SubCategoriaID As Int32, ByVal Hil_Asunto As String)
            SqlHelper.ExecuteNonQuery(ConnectionString, GetFullyQualifiedName("UpdateComunicadorWeb"), PortalId, HiloID, Hil_FechaCreacion, Hil_Status, Hil_UsuarioID, Hil_IDUltimoMensaje, Hil_Cerrado, GetNull(Hil_FechaCierre), Hil_PrioridadId, Hil_CategoriaID, Hil_SubCategoriaID, Hil_Asunto)
        End Sub

        Public Overrides Sub DeleteComunicadorWeb(ByVal PortalId As Integer, ByVal HiloID As Integer)
            SqlHelper.ExecuteNonQuery(ConnectionString, GetFullyQualifiedName("DeleteComunicadorWeb"), PortalId, HiloID)
        End Sub
        Public Overrides Function GetAllComunicadorWebsActivos(ByVal PortalId As Integer) As IDataReader
            Return CType(SqlHelper.ExecuteReader(ConnectionString, GetFullyQualifiedName("GetAllComunicadorWebsActivos"), PortalId), IDataReader)
        End Function
        Public Overrides Function CountAllComunicadorWebsActivos(ByVal PortalID As Integer) As Integer
            Return CType(SqlHelper.ExecuteScalar(ConnectionString, GetFullyQualifiedName("CountAllComunicadorWebsActivos"), PortalID), Integer)
        End Function
        Public Overrides Function CountAllComunicadorWebsCerrados(ByVal PortalID As Integer) As Integer
            Return CType(SqlHelper.ExecuteScalar(ConnectionString, GetFullyQualifiedName("CountAllComunicadorWebsCerrados"), PortalID), Integer)
        End Function
        Public Overrides Function CountComunicadorWebsCerrados(ByVal PortalID As Integer, ByVal UserID As String) As Integer
            Return CType(SqlHelper.ExecuteScalar(ConnectionString, GetFullyQualifiedName("CountComunicadorWebsCerrados"), PortalID, UserID), Integer)
        End Function


        Public Overrides Function GetAllComunicadorWebsCerrados(ByVal PortalId As Integer, ByVal PageSize As Integer, ByVal CurrentPage As Integer) As IDataReader
            Return CType(SqlHelper.ExecuteReader(ConnectionString, GetFullyQualifiedName("GetAllComunicadorWebsCerrados"), PortalId, PageSize, CurrentPage), IDataReader)
        End Function


        ''' PRIORIDADES

        Public Overrides Function GetSGW_Prioridades(ByVal pri_ID As Integer) As IDataReader
            Return CType(SqlHelper.ExecuteReader(ConnectionString, GetFullyQualifiedName("PrioridadesGet"), pri_ID), IDataReader)
        End Function

        Public Overrides Function GetSGW_PrioridadesByPri_PortalID(ByVal pri_PortalID As Integer) As IDataReader
            Return CType(SqlHelper.ExecuteReader(ConnectionString, GetFullyQualifiedName("PrioridadesGetByPri_PortalID"), pri_PortalID), IDataReader)
        End Function

        'Public Overrides Function GetAllSGW_Prioridadess() As IDataReader
        '    Return CType(SqlHelper.ExecuteReader(ConnectionString, GetFullyQualifiedName("PrioridadesGetAll")), IDataReader)
        'End Function

        Public Overrides Function AddSGW_Prioridades(ByVal pri_PortalID As Integer, ByVal pri_Nombre As String, ByVal pri_Peso As Integer) As Integer
            Return CType(SqlHelper.ExecuteScalar(ConnectionString, GetFullyQualifiedName("PrioridadesAdd"), pri_PortalID, pri_Nombre, pri_Peso), Integer)
        End Function

        Public Overrides Sub UpdateSGW_Prioridades(ByVal pri_ID As Integer, ByVal pri_PortalID As Integer, ByVal pri_Nombre As String, ByVal pri_Peso As Integer)
            SqlHelper.ExecuteNonQuery(ConnectionString, GetFullyQualifiedName("PrioridadesUpdate"), pri_ID, pri_PortalID, pri_Nombre, pri_Peso)
        End Sub

        Public Overrides Sub DeleteSGW_Prioridades(ByVal pri_ID As Integer)
            SqlHelper.ExecuteNonQuery(ConnectionString, GetFullyQualifiedName("PrioridadesDelete"), pri_ID)
        End Sub

        ''' Categorias

        Public Overrides Function GetSGW_Categorias(ByVal iDCategoria As Integer) As IDataReader
            Return CType(SqlHelper.ExecuteReader(ConnectionString, GetFullyQualifiedName("CategoriasGet"), iDCategoria), IDataReader)
        End Function

        Public Overrides Function GetAllSGW_Categoriass(ByVal PortalID As Integer) As IDataReader
            Return CType(SqlHelper.ExecuteReader(ConnectionString, GetFullyQualifiedName("CategoriasGetAll"), PortalID), IDataReader)
        End Function

        Public Overrides Function AddSGW_Categorias(ByVal portalID As Integer, ByVal descripcion As String) As Integer
            Return CType(SqlHelper.ExecuteScalar(ConnectionString, GetFullyQualifiedName("CategoriasAdd"), portalID, descripcion), Integer)
        End Function

        Public Overrides Sub UpdateSGW_Categorias(ByVal iDCategoria As Integer, ByVal portalID As Integer, ByVal descripcion As String)
            SqlHelper.ExecuteNonQuery(ConnectionString, GetFullyQualifiedName("CategoriasUpdate"), iDCategoria, portalID, descripcion)
        End Sub

        Public Overrides Sub DeleteSGW_Categorias(ByVal iDCategoria As Integer)
            SqlHelper.ExecuteNonQuery(ConnectionString, GetFullyQualifiedName("CategoriasDelete"), iDCategoria)
        End Sub

        Public Overrides Function GetSGW_SubCategorias(ByVal iDSubcategoria As Integer) As IDataReader
            Return CType(SqlHelper.ExecuteReader(ConnectionString, GetFullyQualifiedName("SubCategoriasGet"), iDSubcategoria), IDataReader)
        End Function

        Public Overrides Function GetSGW_SubCategoriasBySC_PortalID(ByVal sC_PortalID As Integer) As IDataReader
            Return CType(SqlHelper.ExecuteReader(ConnectionString, GetFullyQualifiedName("SubCategoriasGetBySC_PortalID"), sC_PortalID), IDataReader)
        End Function

        Public Overrides Function GetSGW_SubCategoriasBySC_CategoriaID(ByVal sC_CategoriaID As Integer) As IDataReader
            Return CType(SqlHelper.ExecuteReader(ConnectionString, GetFullyQualifiedName("SubCategoriasGetBySC_CategoriaID"), sC_CategoriaID), IDataReader)
        End Function

        Public Overrides Function GetAllSGW_SubCategoriass() As IDataReader
            Return CType(SqlHelper.ExecuteReader(ConnectionString, GetFullyQualifiedName("SubCategoriasGetAll")), IDataReader)
        End Function

        Public Overrides Function AddSGW_SubCategorias(ByVal sC_PortalID As Integer, ByVal sC_CategoriaID As Integer, ByVal sc_Descripcion As String) As Integer
            Return CType(SqlHelper.ExecuteScalar(ConnectionString, GetFullyQualifiedName("SubCategoriasAdd"), sC_PortalID, sC_CategoriaID, GetNull(sc_Descripcion)), Integer)
        End Function

        Public Overrides Sub UpdateSGW_SubCategorias(ByVal iDSubcategoria As Integer, ByVal sC_PortalID As Integer, ByVal sC_CategoriaID As Integer, ByVal sc_Descripcion As String)
            SqlHelper.ExecuteNonQuery(ConnectionString, GetFullyQualifiedName("SubCategoriasUpdate"), iDSubcategoria, sC_PortalID, sC_CategoriaID, GetNull(sc_Descripcion))
        End Sub

        Public Overrides Sub DeleteSGW_SubCategorias(ByVal iDSubcategoria As Integer)
            SqlHelper.ExecuteNonQuery(ConnectionString, GetFullyQualifiedName("SubCategoriasDelete"), iDSubcategoria)
        End Sub


        ''' Mensajes


        Public Overrides Function GetSGW_Mensajes(ByVal mensajeID As Integer) As IDataReader
            Return CType(SqlHelper.ExecuteReader(ConnectionString, GetFullyQualifiedName("MensajesGet"), mensajeID), IDataReader)
        End Function

        Public Overrides Function GetSGW_MensajesByMen_HiloID(ByVal men_HiloID As Integer) As IDataReader
            Return CType(SqlHelper.ExecuteReader(ConnectionString, GetFullyQualifiedName("MensajesGetByMen_HiloID"), men_HiloID), IDataReader)
        End Function

        Public Overrides Function GetAllSGW_Mensajess() As IDataReader
            Return CType(SqlHelper.ExecuteReader(ConnectionString, GetFullyQualifiedName("MensajesGetAll")), IDataReader)
        End Function

        Public Overrides Function AddSGW_Mensajes(ByVal men_Portal As String, ByVal men_UsuarioID As Integer, ByVal men_HiloID As Integer, ByVal men_FechaMensaje As DateTime, ByVal men_Mensaje As String, ByVal men_IpDesde As String) As Integer
            Return CType(SqlHelper.ExecuteScalar(ConnectionString, GetFullyQualifiedName("MensajesAdd"), men_Portal, men_UsuarioID, men_HiloID, men_FechaMensaje, men_Mensaje, GetNull(men_IpDesde)), Integer)
        End Function

        Public Overrides Sub UpdateSGW_Mensajes(ByVal mensajeID As Integer, ByVal men_Portal As String, ByVal men_UsuarioID As Integer, ByVal men_HiloID As Integer, ByVal men_FechaMensaje As DateTime, ByVal men_Mensaje As String, ByVal men_IpDesde As String)
            SqlHelper.ExecuteNonQuery(ConnectionString, GetFullyQualifiedName("MensajesUpdate"), mensajeID, men_Portal, men_UsuarioID, men_HiloID, men_FechaMensaje, men_Mensaje, GetNull(men_IpDesde))
        End Sub

        Public Overrides Sub DeleteSGW_Mensajes(ByVal mensajeID As Integer)
            SqlHelper.ExecuteNonQuery(ConnectionString, GetFullyQualifiedName("MensajesDelete"), mensajeID)
        End Sub

        ''' Configuracion
        ''' 
        Public Overrides Function GetSGW_Configuracion(ByVal idConfig As Integer) As IDataReader
            Return CType(SqlHelper.ExecuteReader(ConnectionString, GetFullyQualifiedName("ConfiguracionGet"), idConfig), IDataReader)
        End Function

        Public Overrides Function GetSGW_ConfiguracionBycfg_Descripcion(ByVal cfg_PortalID As Integer, ByVal cfg_Descripcion As String) As String
            'Return CType(SqlHelper.Executealar(ConnectionString, GetFullyQualifiedName("ConfiguracionGetBycfg_Descripcion"), cfg_PortalID, cfg_Descripcion), String)

            Dim myConnection As New SqlConnection(ConnectionString)
            Dim myCommand As New SqlCommand(GetFullyQualifiedName("ConfiguracionGetBycfg_Descripcion"), myConnection)

            ' Mark the Command as a SPROC
            myCommand.CommandType = CommandType.StoredProcedure

            Dim parameterPortalId As New SqlParameter("@PortalID", SqlDbType.Int)
            parameterPortalId.Value = cfg_PortalID
            myCommand.Parameters.Add(parameterPortalId)

            Dim parameterCFGDesc As New SqlParameter("@cfg_Descripcion", SqlDbType.NVarChar, 255)
            parameterCFGDesc.Value = cfg_Descripcion
            myCommand.Parameters.Add(parameterCFGDesc)


            Dim parameterValor As New SqlParameter("@Valor", SqlDbType.NVarChar, 255)
            parameterValor.Direction = ParameterDirection.Output
            myCommand.Parameters.Add(parameterValor)

            ' Open the database connection and execute the command

            myConnection.Open()

            Dim Result As String
            Result = CStr(myCommand.ExecuteScalar())
            myCommand.Dispose()
            myConnection.Close()
            Return Result

        End Function

        Public Overrides Function GetAllSGW_Configuracions(ByVal Portalid As Integer) As IDataReader
            Return CType(SqlHelper.ExecuteReader(ConnectionString, GetFullyQualifiedName("ConfiguracionGetAll"), Portalid), IDataReader)
        End Function

        Public Overrides Function AddSGW_Configuracion(ByVal cfg_PortalID As Integer, ByVal cfg_Descripcion As String, ByVal cfg_Valor As String) As Integer
            Return CType(SqlHelper.ExecuteScalar(ConnectionString, GetFullyQualifiedName("ConfiguracionAdd"), cfg_PortalID, cfg_Descripcion, cfg_Valor), Integer)
        End Function

        Public Overrides Sub UpdateSGW_Configuracion(ByVal idConfig As Integer, ByVal cfg_PortalID As Integer, ByVal cfg_Descripcion As String, ByVal cfg_Valor As String)
            SqlHelper.ExecuteNonQuery(ConnectionString, GetFullyQualifiedName("ConfiguracionUpdate"), idConfig, cfg_PortalID, cfg_Descripcion, cfg_Valor)
        End Sub

        Public Overrides Sub DeleteSGW_Configuracion(ByVal idConfig As Integer)
            SqlHelper.ExecuteNonQuery(ConnectionString, GetFullyQualifiedName("ConfiguracionDelete"), idConfig)
        End Sub

        Public Overrides Function GetSGW_ConfiguracionIDBycfg_Descripcion(ByVal cfg_PortalID As Integer, ByVal cfg_Descripcion As String) As Integer
            Return CType(SqlHelper.ExecuteScalar(ConnectionString, GetFullyQualifiedName("ConfiguracionGetIDBycfg_Descripcion"), cfg_PortalID, cfg_Descripcion), Integer)
        End Function




#End Region

#Region "Private Methods"

        Private Function GetFullyQualifiedName(ByVal name As String) As String
            Return DatabaseOwner & ObjectQualifier & ModuleQualifier & name
        End Function

        Private Function GetNull(ByVal Field As Object) As Object
            Return DotNetNuke.Common.Utilities.Null.GetNull(Field, DBNull.Value)
        End Function

#End Region

#Region "Lookup data providers"

#End Region


    End Class


End Namespace

